﻿using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data.OleDb;
using System.Data.Common;
using System.IO;
using System.Text;
using System.Text.RegularExpressions;

namespace EnvironmentManagerSystem.Utility
{
    public abstract class OfficeHelper
    {
        /// <summary>
        /// 根据模板生成项目合同
        /// </summary>
        /// <param name="page"></param>
        /// <param name="dt">合同信息</param>
        public static void updateContract(Page page,DataTable dt)
        {
            try
            {
                File.Copy(page.Server.MapPath("合同范本.xls"), page.Server.MapPath(dt.Rows[0]["ProjectName"].ToString() + ".xls"), true);
                string execelConnectionStr = @"Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=""Excel 8.0;HDR=NO;"";Data Source=" + page.Server.MapPath(dt.Rows[0]["ProjectName"].ToString() + ".xls") + ";";
                using (OleDbConnection conn = new OleDbConnection(execelConnectionStr))
                {
                    conn.Open();
                    OleDbCommand cmd = new OleDbCommand();
                    update(conn, cmd, string.Format("UPDATE [Sheet1$] SET F5 = '{0}' WHERE F5 like '%项目名称%'", dt.Rows[0]["ProjectName"].ToString()));
                    update(conn, cmd, string.Format("UPDATE [Sheet1$] SET F5 = '{0}' WHERE F5 like '%甲方名称%'", dt.Rows[0]["Name"].ToString()));
                    update(conn, cmd, string.Format("UPDATE [Sheet1$] SET F5 = '{0}' WHERE F5 like '%生成合同时间%'", dt.Rows[0]["hetongdate"].ToString()));
                    update(conn, cmd, string.Format("UPDATE [Sheet1$] SET F5 = '{0}' WHERE F5 like '%鉴定时间加一年%'", dt.Rows[0]["hetongdate"].ToString() + "至" + Convert.ToDateTime(dt.Rows[0]["hetongdate"].ToString()).AddYears(1).ToString("yyyy年MM月dd日")));
                    update(conn, cmd, string.Format("UPDATE [Sheet1$] SET F4 = '{0}' WHERE F4 like '%住所地址%'", dt.Rows[0]["Address"].ToString()));
                    update(conn, cmd, string.Format("UPDATE [Sheet1$] SET F4 = '{0}' WHERE F4 like '%法人代表%'", dt.Rows[0]["ren"].ToString()));
                    update(conn, cmd, string.Format("UPDATE [Sheet1$] SET F4 = '{0}' WHERE F4 like '%项目联系人%'", dt.Rows[0]["ProjectPerson"].ToString()));
                    update(conn, cmd, string.Format("UPDATE [Sheet1$] SET F3 = '{0}' WHERE F3 like '%联系人手机%'", dt.Rows[0]["Mobil"].ToString()));
                    update(conn, cmd, string.Format("UPDATE [Sheet1$] SET F3 = '{0}' WHERE F3 like '%南岗区马端街%'", dt.Rows[0]["Address"].ToString()));
                    update(conn, cmd, string.Format("UPDATE [Sheet1$] SET F2 = '{0}' WHERE F2 like '%123456789%'", dt.Rows[0]["Phone"].ToString()));
                    update(conn, cmd, string.Format("UPDATE [Sheet1$] SET F5 = '传真：{0}' WHERE F5 like '%传真：%'", dt.Rows[0]["chuanzhen"].ToString()));
                    update(conn, cmd, string.Format("UPDATE [Sheet1$] SET F4 = '{0}' WHERE F4 like '%崔志强（项目生成合同人）%'", dt.Rows[0]["hesuanren"].ToString()));
                    update(conn, cmd, string.Format("UPDATE [Sheet1$] SET F3 = '{0}' WHERE F3 like '%项目生成合同人办公电话%'", dt.Rows[0]["hesuanrenphone"].ToString()));
                    update(conn, cmd, string.Format("UPDATE [Sheet1$] SET F1 = '     本合同甲方委托乙方就《{0}》' WHERE F1 like '%     本合同甲方委托乙方就《项目名称》%'", dt.Rows[0]["ProjectName"].ToString()));
                    update(conn, cmd, string.Format("UPDATE [Sheet1$] SET F1 = '        1．咨询内容： 对《{0}》' WHERE F1 like '%        1．咨询内容： 对《项目名称》'", dt.Rows[0]["ProjectName"].ToString()));

                    if (dt.Rows[0]["Report"].ToString() == "一般报告表" || dt.Rows[0]["Report"].ToString() == "特殊报告表")
                    {
                        update(conn, cmd, "UPDATE [Sheet1$] SET F1 = '  进行技术评估，并出具《环境影响报告表》的技术评估报告。' WHERE F1 like '%  进行技术评估，并出具《环境影响报告书/表（作判断）》的技术评估报告。%'");
                        update(conn, cmd, "UPDATE [Sheet1$] SET F2 = '环境影响报告表' WHERE F2 like '%环境影响报告书/表（作判断）%'");
                        update(conn, cmd, "UPDATE [Sheet1$] SET F1 = '  询工作：在本项目环境影响报告表编制完善后以评估报告的形式提交' WHERE F1 like '%  询工作：在本项目环境影响报告书编制完善后以评估报告的形式提交%'");
                    }
                    else if (dt.Rows[0]["Report"].ToString() == "报告书")
                    {
                        update(conn, cmd, "UPDATE [Sheet1$] SET F1 = '  进行技术评估，并出具《环境影响报告书》的技术评估报告。' WHERE F1 like '%  进行技术评估，并出具《环境影响报告书/表（作判断）》的技术评估报告。%'");
                        update(conn, cmd, "UPDATE [Sheet1$] SET F2 = '环境影响报告书' WHERE F2 like '%环境影响报告书/表（作判断）%'");
                        update(conn, cmd, "UPDATE [Sheet1$] SET F1 = '  询工作：在本项目环境影响报告书编制完善后以评估报告的形式提交' WHERE F1 like '%  询工作：在本项目环境影响报告书编制完善后以评估报告的形式提交%'");
                    }
                    update(conn, cmd, string.Format("UPDATE [Sheet1$] SET F7 = '{0}' WHERE F7 like '%大写核定评估金额%'", chang(Convert.ToDouble(dt.Rows[0]["hedinghetongjine"]) * 10000)));
                    conn.Close();
                }
            }
            catch (Exception ex)
            {
                HttpContext.Current.Response.Write(ex.ToString());
            }
        }

        public static void update(OleDbConnection conn, OleDbCommand cmd, string sql)
        {
            cmd.CommandText = sql;
            cmd.Connection = conn;
            cmd.ExecuteNonQuery();
        }

        /// <summary>
        /// 数字金额转汉字大写金额
        /// </summary>
        /// <param name="moneyDigital">金额(数字)</param>
        /// <returns></returns>
        public static string chang(double Digital)
        {
            //将小写金额转换成大写金额           
            
            String[] MyScale = { "分", "角", "圆", "拾", "佰", "仟", "万", "拾", "佰", "仟", "亿", "拾", "佰", "仟", "兆", "拾", "佰", "仟" };
            String[] MyBase = { "零", "壹", "贰", "叁", "肆", "伍", "陆", "柒", "捌", "玖" };
            String moneyStr = "";
            bool isPoint = false;
            string moneyDigital = Digital.ToString();
            if (moneyDigital.IndexOf(".") != -1)
            {
                moneyDigital = moneyDigital.Remove(moneyDigital.IndexOf("."), 1);
                isPoint = true;
            }
            for (int i = moneyDigital.Length; i > 0; i--)
            {
                int MyData = Convert.ToInt16(moneyDigital[moneyDigital.Length - i].ToString());
                moneyStr += MyBase[MyData];
                if (isPoint == true)
                {
                    moneyStr += MyScale[i - 1];
                }
                else
                {
                    moneyStr += MyScale[i + 1];
                }
            }

            while (moneyStr.Contains("零零"))
                moneyStr = moneyStr.Replace("零零", "零");
            moneyStr = moneyStr.Replace("零亿", "亿");
            moneyStr = moneyStr.Replace("亿万", "亿");
            moneyStr = moneyStr.Replace("零万", "万");
            moneyStr = moneyStr.Replace("零仟", "零");
            moneyStr = moneyStr.Replace("零佰", "零");
            moneyStr = moneyStr.Replace("零拾", "零");
            while (moneyStr.Contains("零零")) 
                moneyStr = moneyStr.Replace("零零", "零");
            moneyStr = moneyStr.Replace("零圆", "圆");
            moneyStr = moneyStr.Replace("零角", "");
            moneyStr = moneyStr.Replace("零分", "");
            moneyStr = moneyStr + "整";
            return moneyStr;
        }

        ///<summary>   
        ///复制指定目录的所有文件   
        ///</summary>   
        ///<param name="sourceDir">原始目录</param>   
        ///<param name="targetDir">目标目录</param>   
        ///<param name="overWrite">如果为true,覆盖同名文件,否则不覆盖</param>   
        ///<param name="copySubDir">如果为true,包含目录,否则不包含</param>   
        public static void CopyFiles(string sourceDir, string targetDir, bool overWrite, bool copySubDir)
        {
            //复制当前目录文件   
            foreach (string sourceFileName in Directory.GetFiles(sourceDir))
            {
                string targetFileName = Path.Combine(targetDir, sourceFileName.Substring(sourceFileName.LastIndexOf("\\") + 1));
                if (File.Exists(targetFileName))
                {
                    if (overWrite == true)
                    {
                        File.SetAttributes(targetFileName, FileAttributes.Normal);
                        File.Copy(sourceFileName, targetFileName, overWrite);
                    }
                }
                else
                {
                    File.Copy(sourceFileName, targetFileName, overWrite);
                }
            }
        }

        /// <summary>
        /// 下载文档
        /// </summary>
        /// <param name="page"></param>
        /// <param name="URL">将下载文档的路径</param>
        public static void DownFile(Page page,string URL)
        {
            try
            {
                FileInfo fi = new FileInfo(URL);
                HttpResponse contextResponse = HttpContext.Current.Response;
                contextResponse.Clear();
                contextResponse.Buffer = true;
                contextResponse.Charset = "GB2312"; //设置了类型为中文防止乱码的出现 
                contextResponse.AppendHeader("Content-Disposition", "attachment;filename=" + page.Server.UrlEncode(URL.Substring(URL.LastIndexOf("\\") + 1))); //定义输出文件和文件名 
                contextResponse.AppendHeader("Content-Length", fi.Length.ToString());
                contextResponse.ContentEncoding = Encoding.Default;
                contextResponse.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。 

                contextResponse.WriteFile(fi.FullName);
                contextResponse.Flush();
                fi.Delete();
                contextResponse.End();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 下载文档
        /// </summary>
        /// <param name="page"></param>
        /// <param name="URL"></param>
        public static void DownFile1(Page page, string URL)
        {
            try
            {
                long lngFileSize;
                byte[] bytBuffer;
                int iReading;
                Stream outStream = page.Response.OutputStream;//get output stream

                //page.Response.ContentType = "application/Zip";
                page.Response.AppendHeader("Connection", "close");
                page.Response.AppendHeader("Content-Disposition", "  attachment;  filename  =" + page.Server.UrlEncode(URL.Substring(URL.LastIndexOf("\\") + 1))); //default file name when download
                FileStream fStream = new FileStream(URL, FileMode.OpenOrCreate, FileAccess.Read);
                lngFileSize = fStream.Length;
                bytBuffer = new byte[(int)lngFileSize];
                while ((iReading = fStream.Read(bytBuffer, 0, (int)lngFileSize)) > 0)
                {
                    outStream.Write(bytBuffer, 0, iReading);
                }
                fStream.Close();
                outStream.Close();
                //File.Delete(URL);
                page.Response.End();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// gridview注册onmouseover事件
        /// </summary>
        /// <param name="gv"></param>
        public static void HelpDv(GridView gv)
        {
            foreach (GridViewRow row in gv.Rows)
            {
                HelpDv(row);
            }
        }

        /// <summary>
        /// gridview注册onmouseover事件
        /// </summary>
        /// <param name="gv"></param>
        public static void HelpDv(GridViewRow row)
        {
            //首先判断是否是数据行
            if (row.RowType == DataControlRowType.DataRow)
            {
                //当鼠标停留时更改背景色
                row.Attributes.Add("onmouseover", "c=this.style.backgroundColor;this.style.backgroundColor='#2BB3D5'");
                //当鼠标移开时还原背景色
                row.Attributes.Add("onmouseout", "this.style.backgroundColor=c");
            }
        }

        /// <summary>
        /// GPS坐标格式转换
        /// </summary>
        /// <param name="Longitude">经度</param>
        /// <param name="Latitude">纬度</param>
        /// <returns></returns>
        public static string GPSTrans(string LongiLatitude)
        {
            double LongiLati = 0;
            string[] strLongiLati=new string[3];
            if (LongiLatitude.Contains('度') && LongiLatitude.Contains('分') && LongiLatitude.Contains('秒'))
                strLongiLati = LongiLatitude.Split('度', '分', '秒');
            if (LongiLatitude.Contains('°') && LongiLatitude.Contains('′') && LongiLatitude.Contains('″'))
                strLongiLati = LongiLatitude.Split('°', '′', '″');
            if (LongiLatitude.Contains('°') && LongiLatitude.Contains('\'') && LongiLatitude.Contains('\"'))
                strLongiLati = LongiLatitude.Split('°', '\'', '\"');
            LongiLati = double.Parse(strLongiLati[0]) + double.Parse(strLongiLati[1]) / 60 + double.Parse(strLongiLati[2]) / 3600;
            return LongiLati.ToString();
        }

        /// <summary>
        /// 验证经纬度格式
        /// </summary>
        /// <param name="JudgeString"></param>
        /// <returns></returns>
        public static bool Regex(string JudgeString)
        {
            string regexString="^(\\d{1,3}\\°\\d{1,2}\\'(\\d{1,2}|\\d{1,2}\\.\\d*)\")|(\\d{1,3}\\°\\d{1,2}\\′(\\d{1,2}|\\d{1,2}\\.\\d*)\\″)|(\\d{1,3}度\\d{1,2}分(\\d{1,2}|\\d{1,2}\\.\\d*)秒)$";
            Regex regex = new Regex(regexString);

            if (!regex.IsMatch(JudgeString))
                return false;
            else
                return true;
        }
    }
}
